package com.erlang.sql.jdbc;

import com.erlang.sql.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * JDBC实现对单表数据增、删、改
 * @author ZGQ
 */
public class JdbcCRUD {
  /**
   * 对单表进行增、删、改
   *
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  @Test
  public void test01() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");

    Connection conn = DriverManager.getConnection("jdbc:mysql:///spring", "root", "1234");
    System.out.println(conn);

    // 从连接中拿到一个Statement对象
    Statement stmt = conn.createStatement();

    // 1.插入记录
    String sql = "insert into jd_user values(null, 'zhaoliu', 'abc')";
    int i = stmt.executeUpdate(sql);
    System.out.println("影响的行数:" + i);

    // 2.修改记录
    sql = "update jd_user set username='tianqi' where username='zhaoliu'";
    i = stmt.executeUpdate(sql);
    System.out.println("影响的行数:" + i);

    // 3.删除记录
    sql = "delete from jd_user where id=4";
    i = stmt.executeUpdate(sql);
    System.out.println("影响的行数:" + i);


    // 释放资源
    stmt.close();
    conn.close();
  }

  /**
   * JDBC实现对单表数据查询
   */
  @Test
  public void test02() throws SQLException, ClassNotFoundException {
    Class.forName("com.mysql.jdbc.Driver");

    Connection conn = DriverManager.getConnection("jdbc:mysql:///spring", "root", "1234");
    Statement stmt = conn.createStatement();

    String sql = "select * from jd_user";
    ResultSet rs = stmt.executeQuery(sql);

    // 内部有一个指针,只能取指针指向的那条记录
    while(rs.next()){// 指针移动一行,有数据才返回true
      int id = rs.getInt("id");
      String name = rs.getString(2);
      String pwd= rs.getString(3);
      System.out.println(id+" "+name+" "+pwd);
    }

    // 关闭资源
    rs.close();
    stmt.close();
    conn.close();
  }

  /**
   * jdbc事务
   */
  @Test
  public void test03() {
    Connection conn = null;
    //捕获异常代码快捷键：ctrl+alt+T
    try {
      //1.注册驱动
      Class.forName("com.mysql.jdbc.Driver");
      //2.获取连接
      conn = DriverManager.getConnection("jdbc:mysql:///spring", "root", "1234");
      //开启事务
      conn.setAutoCommit(false);
      //3.获取发送sql语句的对象
      Statement st = conn.createStatement();
      //4.发送sql语句
      //a-100
      st.executeUpdate("UPDATE jd_account SET money = money - 100 WHERE name='a'");

      ////////////////////发生异常////////////////////////////
      //int x = 1 / 0;


      //b+100
      st.executeUpdate("UPDATE jd_account SET money = money + 100 WHERE name='b'");
      //一切正常，提交事务
      conn.commit();
    } catch (Exception e) {
      e.printStackTrace();
      try {
        //回滚事务
        //为了避免空指针异常这里对conn进行判断
        if(conn != null){
          conn.rollback();
        }
      } catch (Exception e1) {
        e1.printStackTrace();
      }
    }
  }

  @Test
  public void delete() {
    // 需求: 删除id=3的记录
    Connection conn = null;
    Statement stmt = null;

    try {
      // 获得连接
      conn = JDBCUtils.getConnection();
      // 获得发送sql的对象
      stmt = conn.createStatement();
      // 执行sql 获得结果
      String sql = "delete from jd_user where id=3";
      int sum = stmt.executeUpdate(sql);
      // 处理结果
      System.out.println(sum);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // 释放资源
      JDBCUtils.release(conn, stmt, null);
    }
  }

  /**
   * jdbc实现登录
   */
  @Test
  public void login() {
    // 需求: 根据用户名和密码 查询用户信息
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      // 获得连接
      conn = JDBCUtils.getConnection();
      // 获得发送sql的对象
      stmt = conn.createStatement();
      // 执行sql 获得结果
      String uname = "zhangsan";
      String upwd = "123";
      String sql = "select * from jd_user where username='" + uname + "' and password='" + upwd + "'";
      System.out.println(sql);
      rs = stmt.executeQuery(sql);
      // 处理结果
      if (rs.next()) {
        int id = rs.getInt("id");
        String username = rs.getString("username");
        String pwd = rs.getString("password");
        System.out.println(id + ":::" + username + "===" + pwd);
        System.out.println("登录成功");
      } else {
        System.out.println("登录失败");
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // 释放资源
      JDBCUtils.release(conn, stmt, rs);
    }
  }
}
